

CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[PId] [int] DEFAULT 0,
	[Name] [nvarchar](255) DEFAULT '',
	[Alias] [nvarchar](255) UNIQUE NOT NULL,
	[DisplayName] [nvarchar](255) DEFAULT '',
	[Url] [nvarchar](255) DEFAULT '',
	[Detail] [nvarchar](4000) DEFAULT '',
	[Created_Date] [datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
)
GO


CREATE TABLE [dbo].[Tag](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[Alias] [nvarchar](255) UNIQUE  NOT NULL,
	[TagName] [nvarchar](255) DEFAULT '',
	[Created_Date] [datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
)
GO
CREATE TABLE [dbo].[ArticleTag](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[ArticleId] [int],
	[TagId] [int],
	[Created_Date] [datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',	
)
GO
CREATE TABLE [dbo].[Article](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[VersionId] [int] DEFAULT 0,
	[Alias] [nvarchar](255) UNIQUE NOT NULL,
	[Status] [nvarchar](255) DEFAULT '',
	[Name] [nvarchar](255) DEFAULT '',
	[Title] [nvarchar](255) DEFAULT '',
	[Url] [nvarchar](255) DEFAULT '',
	[CategoryId] [int],
	[ImageLink] [nvarchar](255) DEFAULT '',
	[Lead] [nvarchar](4000) DEFAULT '',
	[Detail] [ntext] DEFAULT '',
	[PublishDate] [datetime] DEFAULT GETDATE(),
	[ShowComment] [bit] NOT NULL DEFAULT 1,
	[Author] [nvarchar](255) DEFAULT '',
	[TotalView] [int] DEFAULT 0,
	[History] [ntext] DEFAULT '',	
	[Created_Date] [datetime]  DEFAULT GETDATE(),
	[Created_By] [int]  DEFAULT 0,
	[Last_Modified_At] [datetime]  DEFAULT GETDATE(),
	[Last_Modified_By] [int]  DEFAULT 0,
	[flag] [nvarchar](10)  DEFAULT '',
)
GO
CREATE TABLE [dbo].[ArticleComment](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[ArticleId] [int],
	[UserId] [int],
	[PId] [int] DEFAULT 0,
	[Status] [nvarchar](255) DEFAULT '',
	[Name] [nvarchar](255) DEFAULT '',
	[Email] [nvarchar](255) DEFAULT '',
	[Phone] [nvarchar](255) DEFAULT '',
	[Subject] [nvarchar](4000) DEFAULT '',
	[Detail] [ntext] DEFAULT '',
	[History] [ntext] DEFAULT '',	
	[Created_Date] [datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
)
GO
--unique

ALTER TABLE [dbo].[ArticleTag]  ADD CONSTRAINT UQ_ArticleTag_Unique UNIQUE([ArticleId], [TagId])
GO
--foreign key

ALTER TABLE [dbo].[ArticleComment]  WITH CHECK ADD 
CONSTRAINT [FK_ArticleComment_Article] FOREIGN KEY([ArticleId])REFERENCES [dbo].[Article] ([Id]) on delete cascade on update cascade,
CONSTRAINT [FK_ArticleComment_User] FOREIGN KEY([UserId])REFERENCES [dbo].[User] ([Id]) ON DELETE NO ACTION  ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[Article]  WITH CHECK ADD
CONSTRAINT [FK_Article_User] FOREIGN KEY([Created_By])REFERENCES [dbo].[User] ([Id]) on delete cascade on update cascade,
CONSTRAINT [FK_Article_Category] FOREIGN KEY([CategoryId])REFERENCES [dbo].[Category] ([Id]) on delete cascade on update cascade
GO

ALTER TABLE [dbo].[ArticleTag]  WITH CHECK ADD  
CONSTRAINT [FK_ArticleTag_Tag] FOREIGN KEY([TagId])REFERENCES [dbo].[Tag] ([Id]) on delete cascade on update cascade,
CONSTRAINT [FK_ArticleTag_Article] FOREIGN KEY([ArticleId])REFERENCES [dbo].[Article] ([Id])on delete cascade on update cascade
GO

-- create index
CREATE INDEX [Article_Name] ON [dbo].[Article] ([Name])
GO